
<html><HEAD>
<LINK REL=STYLESHEET HREF="default.css" TYPE="text/css">
<TITLE>
Supported SQL Server datatypes</TITLE>
</HEAD>
<BODY>

<!-- Header -->
<p class="ancestor" align="right"><A HREF="connpbp54.htm">Previous</A>&nbsp;&nbsp;<A HREF="connpbp56.htm" >Next</A>
<!-- End Header -->
<A NAME="BCGEFCFA"></A><h1>Supported SQL Server datatypes</h1>
<A NAME="TI1041"></A><p>The SQL Native Client database interface supports the datatypes
listed in <A HREF="connpbp55.htm#BABCBBGG">Table 9-1</A>. </p>
<A NAME="BABCBBGG"></A><table cellspacing=0 cellpadding=6 border=1 frame="void" rules="all"><caption>Table 9-1: Supported datatypes for Microsoft SQL Server 2005</caption>
<tr><td  rowspan="1"  ><A NAME="TI1042"></A>Binary</td>
<td  rowspan="1"  ><A NAME="TI1043"></A>Real</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1044"></A>Bit</td>
<td  rowspan="1"  ><A NAME="TI1045"></A>SmallDateTime</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1046"></A>Character (fewer
than 255 characters)</td>
<td  rowspan="1"  ><A NAME="TI1047"></A>SmallInt</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1048"></A>DateTime</td>
<td  rowspan="1"  ><A NAME="TI1049"></A>SmallMoney</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1050"></A>Decimal</td>
<td  rowspan="1"  ><A NAME="TI1051"></A>Text</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1052"></A>Float</td>
<td  rowspan="1"  ><A NAME="TI1053"></A>Timestamp</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1054"></A>Identity</td>
<td  rowspan="1"  ><A NAME="TI1055"></A>TinyInt</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1056"></A>Image</td>
<td  rowspan="1"  ><A NAME="TI1057"></A>VarBinary(<i>max</i>)</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1058"></A>Int</td>
<td  rowspan="1"  ><A NAME="TI1059"></A>VarBinary(<i>n</i>)</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1060"></A>Money</td>
<td  rowspan="1"  ><A NAME="TI1061"></A>VarChar(<i>max</i>)</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1062"></A>Numeric</td>
<td  rowspan="1"  ><A NAME="TI1063"></A>VarChar(<i>n</i>)</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1064"></A>NVarChar(<i>max</i>)</td>
<td  rowspan="1"  ><A NAME="TI1065"></A>XML</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1066"></A>NVarChar(<i>n</i>)</td>
<td  rowspan="1"  >&nbsp;</td>
</tr>
</table>
<A NAME="TI1067"></A><p>The <b>XML</b> datatype is a built-in datatype
in SQL Server 2005 that enables you to store XML documents and fragments
in a SQL Server database. The <b>XML</b> datatype
maps to the PowerScript <b>String</b> datatype. You
can use this datatype as a column type when you create a table,
as a variable, parameter, or function return type, and with <b>CAST</b> and <b>CONVERT</b> functions.</p>
<A NAME="TI1068"></A><p>Additional datatypes are supported for SQL Server 2008. For
more information, see <A HREF="connpbp61.htm#BCGJBJHD">"Support for new datatypes
in SQL Server 2008"</A>.</p>
<p><img src="images/note.gif" width=17 height=17 border=0 align="bottom" alt="Note"> <span class=shaded>Datatype conversion</span> <A NAME="TI1069"></A>When you retrieve or update columns, PowerBuilder converts
data appropriately between the Microsoft SQL Server datatype and
the PowerScript datatype. Keep in mind, however, that similarly
or identically named SQL Server and PowerScript datatypes do <i>not</i> necessarily
have the same definitions.</p>
<A NAME="TI1070"></A>For information about the definitions of PowerScript
datatypes, see the <i>PowerScript Reference</i>
.</p>
<A NAME="TI1071"></A><p>In SQL Server 2005, the <b>VarChar</b>(<i>max</i>), <b>NVarChar</b>(<i>max</i>),
and <b>VarBinary</b>(<i>max</i>) datatypes
store very large values (up to 2^31 bytes). The <b>VarChar</b>(<i>max</i>)
and <b>NVarChar</b>(<i>max</i>) datatypes
map to the PowerScript <b>String</b> datatype and
the <b>VarBinary</b>(<i>max</i>) datatype
maps to the PowerScript <b>Blob</b> datatype. You
can use these datatypes to obtain metadata, define new columns,
and query data from the columns. You can also use them to pipeline
data.</p>
<A NAME="TI1072"></A><h4>Working with large data values</h4>
<A NAME="TI1073"></A><p>For
large data values of datatypes <b>Text</b>, <b>NText</b>, <b>Image</b>, <b>Varchar(max)</b>, <b>NVarchar(max)</b>, <b>VarBinary(max)</b>,
and <b>XML</b>, the SNC interface supports reading data
directly from the database using an embedded SQL statement.</p>
<A NAME="TI1074"></A><p>Example 1:<p><PRE> select image_col into :blob_var from mytable where key_col = 1;</PRE></p>
<A NAME="TI1075"></A><p>Example 2:<p><PRE> declare cur cursor for select id, image_col from mytable;<br>open cur;<br>fetch cur into :id_var, :blob_var;</PRE></p>
<A NAME="TI1076"></A><p>If the result set contains a large datatype of type <b>Text</b> or <b>Varchar(max)</b>,
using ANSI encoding, you must set a maximum size for each large
value using the PBMaxBlobSize database parameter. For other large
datatypes, there is no limitation on the size of the data. The SNC
interface retrieves all the data from the database if there is sufficient
memory.</p>
<A NAME="TI1077"></A><p>The SNC interface supports inserting and updating values of
large datatypes using embedded SQL <b>INSERT</b> and <b>UPDATE</b> statements.
You must set the DisableBind database parameter to 0 to enable the
SNC interface to bind large data values. For example:<p><PRE> Insert into mytable (id, blob_col) values(1, :blob_var);</PRE><PRE> Update mytable set blob_col = :blob_var where id = 1;</PRE></p>

